﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using PublicTemplateEditor.Helper;
using PublicTemplateEditor.Mold;
using System.Windows;

namespace PublicTemplateEditor.DataAccess
{
    public class TemplateDA
    {
        #region  模板操作sql
        //插入模板
        private  const string InsertTemplateSql = "insert into m_board(id,mname,msound,mbg,gid) values ('{0}','{1}','{2}','{3}',{4}) ;" ;
        //插入模板页面关系
        private  const string InsertTemplateRelationSql = "insert into m_MPRelations(mid,pid,psort)values('{0}','{1}',{2});";
        //删除模板关系
        private  const string DeleteTemplateRelationSql = "Delete from m_MPRelations where mid='{0}';";
        //删除模板
        private  const string DeleteTemplateSql = "delete from m_board where id='{0}';";
        //更新模板
        private  const string UpdateTemplateSql = "Update m_board set mname='{0}',msound='{1}',mbg='{2}',gid={3} where id = '{4}';";
        //获取模板列表
        private const string GetTemplateListSql = "select  id,mname,thumbimg from (select a.id as id,a.mname as mname,b.thumbimg as thumbimg   from m_board a inner join m_file b on a.mbg=b.name where b.type = 18  order by modify desc) limit {0} offset {1};";
        
        //private const string GetTemplateListSql = "select  id,mname,thumbimg from (select a.id as id,a.mname as mname,null as thumbimg   from m_board a   order by modify desc) limit {0} offset {1};";
        
        //获取模板所属页面列表
        private const string GetPageListSql = "select id,pname,thumbnail,b.psort from m_page a inner join M_MPRelations b  on a.id=b.pid  where mid='{0}' order by psort asc;";
        //获取模板所属页面详细信息列表
        private const string GetPageInfoListSql = "select  a.pname as name,a.id as mark,a.month,pbg,screenshots,psort,thumbnail,modify,intime from m_page  a  inner join m_MPRelations c on c.pid=a.id where c.mid='{0}' order by psort asc";
        //获取模板
        private const string GetTemplateSql = "select id,mname,msound,mbg,gid from m_board where id ='{0}';";
        //判断模板是否存在
        private const string TemplateExit = "select count(1) from m_board where id = '{0}'";
        //模板总数
        private const string GetTemplateTotalSql = "select count(1) from m_board";
        #endregion

        #region 新增模板

        /// <summary>
        /// 插入模板
        /// </summary>
        /// <param name="template"></param>
        /// <param name="dictRelation"></param>
        public static  void  InsertTemplate(TemplateInfo template,List<object[]> dictRelation)
        {
            string sql = string.Format(InsertTemplateSql, template.ID,  CommonHelper.Encode(template.MName), template.MSound, template.MBG, template.GID);
           
            StringBuilder relSql = new StringBuilder();
            foreach(object[] obj in dictRelation)
            {
                relSql.Append(string.Format(InsertTemplateRelationSql,template.ID,obj[0],obj[1]));               
            }
             
            CommonAccess.sqlite.ExeSql(sql + relSql.ToString());
        }

        /// <summary>
        /// 先删除模板，然后插入新模板
        /// </summary>
        /// <param name="template"></param>
        /// <param name="dictRelation"></param>
        public static void InsertTemplateWithDelete(TemplateInfo template, List<object[]> dictRelation)
        {
            string DeleteSql = string.Format(DeleteTemplateSql, template.ID) + string.Format(DeleteTemplateRelationSql, template.ID);
            string sql = string.Format(InsertTemplateSql, template.ID, CommonHelper.Encode(template.MName), template.MSound, template.MBG, template.GID);
            
            StringBuilder relSql = new StringBuilder();
            foreach (object[] obj in dictRelation)
            {
                relSql.Append(string.Format(InsertTemplateRelationSql, template.ID, obj[0], obj[1]));
            }

            CommonAccess.sqlite.ExeSql(DeleteSql + sql + relSql.ToString());
        }
        #endregion

        #region 更新模板

        /// <summary>
        /// 更新模板
        /// </summary>
        /// <param name="template"></param>
        /// <param name="dictRelation"></param>
        public static void UpdateTemplate(TemplateInfo template, List<object[]> dictRelation)
        {
            string sql = string.Format(UpdateTemplateSql, CommonHelper.Encode(template.MName), template.MSound, template.MBG, template.GID, template.ID);
     
            StringBuilder relSql = new StringBuilder();
            foreach(object[] obj in dictRelation)
            {
                  relSql.Append(string.Format(InsertTemplateRelationSql,template.ID,obj[0],obj[1]));               
            }
             
            CommonAccess.sqlite.ExeSql(sql + string.Format(DeleteTemplateRelationSql, template.ID) + relSql.ToString());
        }

        #endregion

        #region 获取模板
        /// <summary>
        /// 获取模板列表
        /// </summary>
        /// <param name="limit"></param>
        /// <param name="offset"></param>
        /// <returns></returns>
        public static Dictionary<string, MaterialInfo> GetTemplateList(int limit, int offset)
        {
            Dictionary<string, MaterialInfo> brushList = new Dictionary<string, MaterialInfo>();
            List<object[]> objList = CommonAccess.sqlite.GetArray(string.Format(GetTemplateListSql, limit, offset));
            if (objList == null) return null;
            foreach (object[] obj in objList)
            {
                MaterialInfo materail = new MaterialInfo();
                materail.Guid = obj[0].ToString();
                materail.Source = CommonHelper.GetImageSource(CommonHelper.ConvertToByte(obj[2]));
                materail.Name = CommonHelper.Decode(obj[1].ToString());
                materail.Type = EditTypes.TemplateThumbnail;
                brushList.Add(materail.Guid,materail);
            }

            return brushList;
        }

        /// <summary>
        /// 获取模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static TemplateInfo GetTemplate(string id)
        {
            if (id.IsNull()) return null;
            string sql = string.Format(GetTemplateSql, id);
            object[] obj = CommonAccess.sqlite.GetRow(sql);
            if (obj == null)
                return null;
            TemplateInfo temp = new TemplateInfo()
            {
                ID = obj[0].ToString(),
                MName = CommonHelper.Decode(obj[1].ToString()),
                MSound = obj[2].ToString(),
                MBG = obj[3].ToString(),
                GID = CommonHelper.ConvertToInt(obj[4])
            };

            return temp;
        }

        /// <summary>
        /// 模板所属页面列表，模板编辑所用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<MaterialInfo> GetPageList(string id)
        {
            List<MaterialInfo> brushList = new List<MaterialInfo>();
            string sql = string.Format(GetPageListSql, id);
            List<object[]> objList = CommonAccess.sqlite.GetArray(sql);
            if (objList == null) return null;
            foreach (object[] obj in objList)
            {
                MaterialInfo materail = new MaterialInfo();
                materail.Guid = obj[0].ToString();
                materail.Source = CommonHelper.GetImageSource(CommonHelper.ConvertToByte(obj[2]));
                materail.Name = CommonHelper.Decode(obj[1].ToString());
                brushList.Add(materail);
            }

            return brushList;
        }

        /// <summary>
        /// 模板所属页面列表,包含页面详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<PageInfo> GetPageInfoList(string id)
        {
            List<PageInfo> pageList = new List<PageInfo>();
            string sql = string.Format(GetPageInfoListSql, id);
            List<object[]> objList = CommonAccess.sqlite.GetArray(sql);
            if (objList == null) return null;

            foreach (object[] obj in objList)
            {
                PageInfo page = new PageInfo();
                page.PageID = obj[1].ToString();
                page.PageName = obj[0].ToString();
                page.Month = obj[2].ToString();
                page.BackgroundName = obj[3].ToString();
                page.Screenshots = CommonHelper.ConvertToByte(obj[4]);
                page.Thumbnail = CommonHelper.ConvertToByte(obj[6]);
                page.UpdateTime = obj[7].ToDateTime();
                page.Intime = obj[8].ToDateTime();
                pageList.Add(page);
            }

            return pageList;
        }

        /// <summary>
        /// 将模板的插入时间和更新时间设为相同，防止重复上传
        /// </summary>
        /// <param name="id"></param>
        public static void UpdateTime(string id)
        {
            string sql = "update m_page set  intime=modify  where id in (select pid from m_MPRelations where mid= '{0}') and intime!=modify";
            CommonAccess.sqlite.ExeSql(string.Format(sql,id));
        }

        /// <summary>
        /// 获取该模板与页面的关系 object[] 页面ID+排序值
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<object[]> GetRelations(string id)
        {
            List<object[]> relations = new List<object[]>();
            string GetRelationsSql = "select pid,psort from m_mprelations where mid='{0}' order by psort asc;";
            List<object[]> objList = CommonAccess.sqlite.GetArray(string.Format(GetRelationsSql, id));

            if (objList == null)
                return null;
            foreach (object[] obj in objList)
            {
                relations.Add(new object[] { obj[0], obj[1] });
            }
            return relations;
        }

        /// <summary>
        /// 获取模板总数
        /// </summary>
        /// <returns></returns>
        public static int GetTemplateTotal()
        {
            object obj = DataAccess.CommonAccess.sqlite.GetScalar(GetTemplateTotalSql);
            return CommonHelper.ConvertToInt(obj);
        }


        #endregion

        #region  删除模板

        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteTemplate(string id)
        {
            string sql = string.Format(DeleteTemplateSql, id)+ string.Format(DeleteTemplateRelationSql,id) ;
            DataAccess.CommonAccess.sqlite.ExeSql(sql);
        }

        /// <summary>
        /// 删除模板、相应页面、相应元素
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteTemplateAll(string id)
        {
            //模板对应页面
            List<object[]> objList = GetRelations(id);
            //删除模板以及关系
            string sql = string.Format(DeleteTemplateSql, id) + string.Format(DeleteTemplateRelationSql, id);
            DataAccess.CommonAccess.sqlite.ExeSql(sql);
            string pageID = string.Empty;
            //删除相应页面以及元素。如果被其他模板使用，则不能删除
            foreach (object[] obj in objList)
            {
                pageID = obj[0].ToString();
                if (!PageDA.CheckUsed(pageID))
                {
                    PageDA.DeletePageWithNoCheck(pageID);
                }
            }
        }
        #endregion

        /// <summary>
        /// 判断模板是否存在
        /// </summary>
        /// <param name="id"></param>
        public static bool templateExit(string id)
        {
            string sql = string.Format(TemplateExit, id);
            bool ret  = false;
            
            int total = DataAccess.CommonAccess.sqlite.GetScalar(sql).ToInt();
            ret = total > 0 ? true : false;

            return ret;
        }

        
        /// <summary>
        /// 复制模板
        /// </summary>
        /// <param name="id"></param>
        public static bool CopyTemplate(string id)
        {
            bool ret = false;

            #region 复制模板配置
            string sql = string.Format(GetTemplateSql, id);
            object[] obj = CommonAccess.sqlite.GetRow(sql);
            if (obj == null) return ret ;

            TemplateInfo temp = new TemplateInfo()
            {
                ID = CommonHelper.GetGuid(),
                MName = "复件 " + CommonHelper.Decode(obj[1].ToString()),
                MSound = obj[2].ToString(),
                MBG = obj[3].ToString(),
                GID = CommonHelper.ConvertToInt(obj[4])
            };
            #endregion 

            #region 复制页面以及关系
            List<object[]> relations = GetRelations(id);
            List<object[]> newRelations = new List<object[]>();
            string pageID = string.Empty;
            string newPageID = string.Empty;
            string sort = string.Empty;

            for (int i = 0; i < relations.Count; i++)
            {
                object[] objRelation = relations[i];
                pageID = objRelation[0].ToString();
                sort = objRelation[1].ToString();
                newPageID = PageDA.CopyPage(pageID);
                newRelations.Add(new object[] { newPageID,sort});
            }
             
            #endregion

            InsertTemplate(temp, newRelations);
            return true;
        }

      

     
    }
}
